Homework 6 - Creating a resume database

This document contains information about a database containing people, skills, and peopleskills tables. These tables are represented as a Crows-Foot diagram and some lists along with descriptions for those sections. At the end there is a reflection and a read me.

GITHUB URL: https://github.com/cmsc-vcu/cmsc408-fa2025-hw6-CaptnKristmas

from helpers import create_database_engine, run_sql_and_return_df, run_sql_and_return_html, create_db_wrapper, execute_ddl_from_file
from itables import show

# Load these variables from .env file.
config_map = {
  'user': "MYSQL_USERNAME",
  'password': "MYSQL_PASSWORD",
  'host': "MYSQL_HOSTNAME",
  'database': "HW6_DB_NAME"
}

cnx,config = create_db_wrapper( config_map )
  

def show_result_set( sql, **kwargs ):
    df = run_sql_and_return_df( cnx,sql )
    show(df,**kwargs)

Overview and description

The database contains 3 tables, people, skills, and peopleskills. The first covers the employees of the company, the second possible skills, and last tracks the skills earned. As such there is a relationship between people and peopleskills as well as skills and peopleskills. This allows us to connect the skills possibly earned with the employees and store what people have earned in people skills.

We were tasked with creating this database using mySQL, Python, a poetry shell, and multiple libraries listed in the readme. Following this we were tasked with completing this report that breaks down contents of the database via a Crows-Foot diagram, descriptions and some lists. Upon completion we rendered this document as an html that you may view.

Crows-foot diagram

This diagram represents the current database as a whole. Do to limitations we are unable to represent nullable and non nullable values and are also unable to represent varchar directly. This is instead just ignored and replaced with string respectively. We are able to represent the foreign keys and the primary keys on the diagram.

The connection between people and peopleskills as well as skills and peopleskills is represented via the diagram. It shows how exactly one person can have multiple skills tracked and exactly one skill can be tracked across many people.

erDiagram
    PEOPLE ||--o{ PEOPLESKILLS : "has"
    SKILLS ||--o{ PEOPLESKILLS : "tracks"

    PEOPLE {
        int id PK
        string first_name
        string last_name
        string email
        string linkedin_url
        string headshot_url
        string discord_handle
        string brief_bio
        date date_joined
    }

    SKILLS {
        int id PK
        string name
        string description
        string tag
        string url
        string time_commitment
    }

    PEOPLESKILLS {
        int id PK
        int skills_id FK
        int people_id FK
        date date_acquired
    }

Loading the database

ddl_file_name = "./my-ddl.sql"
messages,errors = execute_ddl_from_file( ddl_file_name, cnx)

if errors:
    for error in errors:
        print(f"{error}<br/>")
else:
    print(f"No errors detected while loading: {ddl_file_name}")

No errors detected while loading: ./my-ddl.sql

Examples of data in the database

The following sections provide an overview of the schema including table names, and number of rows and columns in each table.

For each table, a description of each table is presented along with it’s contents.